using System;
using System.Data;
using System.Collections;
using System.Reflection;

using GURUCORE.Framework.Core;
using GURUCORE.Framework.Core.Data;
using GURUCORE.Framework.DataAccess.DataSource;
using GURUCORE.Framework.DataAccess.ORMapping;

using System.Data.OleDb;
using System.Data.SqlClient;
using System.Diagnostics;

namespace GURUCORE.Framework.DataAccess
{
    /// <summary>
    /// LOCKEVN: This class is not a part of GURUCORE framework. It is here to resolve AlphanamICT specific projects.
    /// This class is obsolete, and will be convert into GFD.DBHelper class.
    /// </summary>
    [Obsolete("Brand for PROCON, WORK ONLY WITH SQL SERVER, will be convert to GFD.DBHelper")]    
    public static class DataHelper
    {
        public static string InferDataConnectionString()
        {
            //return ((SqlDataSource)AppContext.GetInstance().GetSharedObject(
            //    GURUCORE.Framework.DataAccess.DataSource.AbstractDataSource.DEFAULT_DATASOURCE)).ConnectionString;
                        
            // TODO: get the connection string of default DB connection here
            string strConnectionStringForReport = "Provider=SQLOLEDB.1;Password=isales;Persist Security Info=True;User ID=isales;Initial Catalog=iSALES;Data Source=192.168.1.1";
            return strConnectionStringForReport;
        }

        /// <summary>
        /// Thachnn: 14/Oct/2005
        /// Merge multi DataTables (have same schema) from a dataset into only one DataTable
        /// </summary>
        /// <param name="pdst">DataSet contain table(s) to merge</param>
        /// <returns>result DataTable if OK, empty DataTable if fail or error</returns>
        public static DataTable MergeManyDataTablesIntoOne(DataSet pdst)
        {
            DataTable dtbRet = new DataTable();
            try
            {
                if (pdst.Tables.Count > 0)
                {
                    dtbRet = pdst.Tables[0].Clone();

                    foreach (DataTable table in pdst.Tables)
                    {
                        /// Don't change this method to Rows.AddRange please. Thachnn.
                        foreach (DataRow drow in table.Rows)
                        {
                            dtbRet.ImportRow(drow);
                        }
                    }
                }
            }
            catch //(Exception ex)
            {
                // throw ex;
            }
            return dtbRet;
        }

        /// <summary>
        /// Thachnn : 15/Oct/2005
        /// Browse the DataTable, get distinct all value of column with provided named.
        /// </summary>
        /// <param name="pdtb">DataTable to collect values</param>
        /// <param name="pstrColumnName">COlumn Name in pdtb DataTable to collect values from</param>
        /// <returns>ArrayList of object, collect from pdtb's column named pstrColumnName. Empty ArrayList if error or not found any row in pdtb.</returns>
        public static ArrayList GetColumnValuesFromTable(DataTable pdtb, string pstrColumnName)
        {
            ArrayList arrRet = new ArrayList();
            try
            {
                foreach (DataRow drow in pdtb.Rows)
                {
                    object objGet = drow[pstrColumnName];
                    if (!arrRet.Contains(objGet))
                    {
                        arrRet.Add(objGet);
                    }
                }
            }
            catch
            {
                arrRet.Clear();
            }
            return arrRet;
        }
            
		static private string dsn = string.Empty;
		
        ///// <summary>
        ///// static counstructor
        ///// </summary>
        //static DataHelper()
        //{			
        //    //dsn = @"Auto Translate=True;User ID=123otc;Tag with column collation when possible=False;Data Source=""10.0.0.1"";Password=cungtien;Initial Catalog=123otc;Use Procedure for Prepare=1;Provider=""SQLOLEDB.1"";Persist Security Info=True;Workstation ID=UNKNOWN;Use Encryption for Data=False;Packet Size=4096";
        //}

		#region Static Methods

		static public void SetDSN(string datasource, string database, string username, string password)
		{
			dsn = string.Format(@"Auto Translate=True;User ID={0};Tag with column collation when possible=False;Data Source=""{1}"";Password={2};Initial Catalog={3};Use Procedure for Prepare=1;Provider=""SQLOLEDB.1"";Persist Security Info=True;Workstation ID=UNKNOWN;Use Encryption for Data=False;Packet Size=4096",
				username,
				datasource,
				password,
                database
				);
		}
        static public void SetDSN(string p_sDSN)
        {
            dsn = p_sDSN;
        }


		/// <summary>
		/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database
		/// </summary>
		/// <param name="sql">A valid sql statement</param>
		/// <param name="parameters">An array of OleDbParamters used to execute the command</param>
		/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>


		static public object ExecuteScalar( string sql, params OleDbParameter[] parameters)
		{
			using (OleDbConnection connection = new OleDbConnection(dsn))
			{
				connection.Open();
				using ( OleDbCommand command = new OleDbCommand( sql, connection))
				{
					object o = null;
					foreach( OleDbParameter p in parameters) 
					{
						command.Parameters.Add( p);
					}
					o = command.ExecuteScalar();
					command.Parameters.Clear();
                    //if (null  == o)
                    //    throw ( new Exception());
					return (o);
				}
			}	
		}


		static public object ExecuteNonQuery( string sql, params OleDbParameter[] parameters)
		{
			using (OleDbConnection connection = new OleDbConnection(dsn))
			{
				connection.Open();
				using ( OleDbCommand command = new OleDbCommand( sql, connection))
				{
					object o = null;
					foreach( OleDbParameter p in parameters) 
					{
						command.Parameters.Add( p);
					}
					o = command.ExecuteNonQuery();
					command.Parameters.Clear();
                    //if (null  == o)
                    //    throw ( new NoDataException());
					return (o);
				}
			}	
		}


		/// <summary>
		/// Execute a SqlCommand (that returns a resultset and takes parameters) against the database. 
		/// </summary>
		/// <param name="sql">A valid sql statement</param>
		/// <param name="parameters">An array of OleDbParamters used to execute the command</param>
		/// <returns>A dataset containing the resultset generated by the command</returns>
		static public DataSet GetDataSet( string sql, params OleDbParameter[] parameters) 
		{
			using (OleDbConnection connection = new OleDbConnection(dsn))
			{
				connection.Open();
				using (OleDbDataAdapter adapter = new OleDbDataAdapter())
				{
					using ( OleDbCommand command = new OleDbCommand( sql, connection))
					{
						foreach( OleDbParameter p in parameters) 
						{
							command.Parameters.Add( p);
						}
						adapter.SelectCommand = command;
						DataSet ds = new DataSet();
						adapter.Fill(ds);
						command.Parameters.Clear();
						return( ds );
					}
				}
			}
		}
		/// <summary>
		/// Execute a SqlCommand (that returns a resultset and takes parameters) against the database. 
		/// </summary>
		/// <param name="sql">A valid sql statement</param>
		/// <param name="parameters">An array of OleDbParamters used to execute the command</param>
		/// <returns>A first DataTable of the dataset containing the resultset generated by the command</returns>
		static public DataTable GetDataTable( string sql, params OleDbParameter[] parameters)
		{
			return ( GetDataSet( sql, parameters).Tables[0] );
		}


        [Obsolete("Brand for PROCON, WORK ONLY WITH SQL SERVER")]
        static public DataSet GetDataSetBySP(string spcommand, params IDataParameter[] parameters)
        {
            using (OleDbConnection connection = new OleDbConnection(dsn))
            {
                connection.Open();

                IDbDataAdapter adapter = new OleDbDataAdapter();

                using (IDbCommand command = new OleDbCommand(spcommand, connection))
                {
                    foreach (IDataParameter p in parameters)
                    {
                        command.Parameters.Add(p);
                    }

                    // NOTE: add by parameters, use StoredProcedure
                    command.CommandType = CommandType.StoredProcedure;
                    adapter.SelectCommand = command;

                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    command.Parameters.Clear();
                    return (ds);
                }
                
            }
        }

        [Obsolete("Brand for PROCON, WORK ONLY WITH SQL SERVER")]
        static public DataSet GetDataSetBySPText(string spcommand)
        {
            using (OleDbConnection connection = new OleDbConnection(dsn))
            {
                connection.Open();

                IDbDataAdapter adapter = new OleDbDataAdapter();

                using (IDbCommand command = new OleDbCommand(spcommand, connection))
                {   
                    // NOTE: add param by Text clause, use COmmandType = TEXT
                    command.CommandType = CommandType.Text;
                    adapter.SelectCommand = command;

                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    command.Parameters.Clear();
                    return (ds);
                }

            }
        }

		#endregion


      


	}
}
